package com.onemore.task.model;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.voovan.db.JdbcOperate;
import org.voovan.tools.log.Logger;

import com.alibaba.fastjson.JSONObject;
import com.onemore.task.JdbcGlobal;
import com.onemore.task.OneConfig;

public class ModelB2CErpWms extends JdbcGlobal{
	final static private String fields[] = new String[] {"SyncBillId","BILLOUTDATE","QTYIN","SL"};

	static public boolean isNotExist()
	{
		return validateTableExist("ONE_B2C_ERPWMS");
	}
	static public boolean isNotExist(String tableName)
	{
		return validateTableExist(tableName);
	}
	static private String getFrom()
	{
		String filterDate = "";
		String cangku = "'09001','09002'";
		String ycdfilter = "'1018','2001'";
		String starDate = null;//req.getParameter("starDate");
		String endDate = null;//req.getParameter("endDate");
		Properties properties= OneConfig.getProperties();
		if(properties != null) {
			String str = OneConfig.getProperties().getProperty("cangku");
			if(str != null) {
				cangku = str;
			}

			if(starDate == null) {
				str = OneConfig.getProperties().getProperty("starDate");
				if(str != null) {
					starDate = str;
				}
			}

			str = OneConfig.getProperties().getProperty("ycdfilter");
			if(str != null) {
				ycdfilter = str;
			}
		}
		if(starDate != null && starDate.length() > 0) {
			filterDate = " RQ>='"+starDate+"' AND";
		}else {
			starDate = "2018";
		}
		return "select *from( SELECT max(DJBH) as DJBH, YDJH as YDJH, CONVERT(VARCHAR(11), MAX(RQ), 120) AS CDate, sum(SL) as SL FROM pfxhd e GROUP BY YDJH union all select max(DJBH) as DJBH, YDJH as YDJH, CONVERT(VARCHAR(11), MAX(RQ), 120) AS CDate, sum(SL) as SL from pfthd e GROUP BY YDJH union all select max(DJBH) as DJBH, YDJH as YDJH, CONVERT(VARCHAR(11), MAX(RQ), 120) AS CDate, sum(SL) as SL from spycd e GROUP BY YDJH) erp right JOIN ONE_B2C_WMS wms on erp.YDJH = wms.SyncbillId WHERE billdate > '"+starDate+"' AND ( erp.DJBH IS NULL OR erp.SL != wms.QTYIN OR erp.YDJH IS NULL) ";
	}
	static public List<Map<String, Object>> getList(String starDate,String endDate) throws SQLException
    {
		String filterDate = "";
		if(starDate != null && starDate.length() > 0) {
			filterDate = " AND billdate>='"+starDate+"'";
		}
		if(endDate != null && endDate.length() > 0) {
			filterDate += " AND billdate<='"+endDate+"'";
		}
		String sql = "SELECT * FROM ONE_B2C_ERPWMS where 1=1"+filterDate;
    	List<Map<String, Object>> manyMaps = null;
		manyMaps = getJdbcOperate().queryMapList(sql);
		return manyMaps;
    }
	static public void dropTable() throws SQLException
	{
		String sql = "drop table ONE_B2C_ERPWMS";
    	getJdbcOperate().update(sql);
	}
	static public void importTable() throws SQLException
	{
		if(!isNotExist())dropTable();
		createTable();
	}
    static public void createTable() throws SQLException
    {
    	String sql = "select * into ONE_B2C_ERPWMS from ("+getFrom()+") t";
//		"PRIMARY KEY ([BillId])\r\n" + 
    	getJdbcOperate().update(sql);
    }
}
